﻿Imports System.Data.SqlClient
Public Class CKetNoi
    'Public Shared connectString = "workstation id=qlBanHangVBNet.mssql.somee.com;packet size=4096;user id=btnthanh94_SQLLogin_1;pwd=ommiliirkd;data source=qlBanHangVBNet.mssql.somee.com;persist security info=False;initial catalog=qlBanHangVBNet"
    'Public Shared connectString = "Data Source=192.168.1.9,49172;Initial Catalog=qlBanHangVBNet;User ID=QLBanHang;Password=123456789"
    Public Shared connectString = "Data Source=HAZTARRES-PC\HAZTARRES;Initial Catalog=qlBanHangVBNet111;Integrated Security=True"
    'Public Shared connectString = "Data Source=192.168.1.9,49172;Initial Catalog=qlBanHangVBNet;User ID=QLBanHang;Password=123456789"
    'Public Shared connectString = "Data Source= .\SQLEXPRESS;Initial Catalog=qlBanHangVBNet;Integrated Security=True"

    Public Shared Connect As New SqlConnection(connectString)

    Public Shared Function GetData(sql As String) As DataTable
        Dim dt As New DataTable

        Dim adapter As New SqlDataAdapter(sql, Connect)

        adapter.Fill(dt)

        Return dt
    End Function

    Public Shared Function LoadBang(tenStore As String) As DataTable
        Dim dt As New DataTable

        Dim adapter As New SqlDataAdapter(tenStore, Connect)

        adapter.Fill(dt)

        Return dt
    End Function

    'update, delete, insert
    Public Shared Function ExecNonQuery(sql As String) As Integer
        Dim result As Integer

        Connect.Open()
        Dim query As New SqlCommand(sql, Connect)
        query.ExecuteNonQuery()
        Connect.Close()

        Return result
    End Function

	Public Shared Function TimKiemMa(tenStore As String, dsThamSo As List(Of SqlParameter)) As Object
        Dim kq As Object

        Connect.Open()
        Dim cmd As New SqlCommand(tenStore, Connect)

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddRange(dsThamSo.ToArray())
        kq = cmd.ExecuteScalar()
        Connect.Close()

        Return kq
    End Function
	
    Public Shared Function ExecQuery(sql As String, dsVal As List(Of SqlParameter))
        Dim result As Integer

        Connect.Open()
        Dim query As New SqlCommand(sql, Connect)
        query.Parameters.AddRange(dsVal.ToArray())
        result = query.ExecuteNonQuery()
        Connect.Close()

        Return result
    End Function

    Public Shared Function ExecQueryStoreProc(StoreProc As String, dsVal As List(Of SqlParameter))
        Dim result As Integer

        Connect.Open()
        Dim query As New SqlCommand(StoreProc, Connect)

        query.CommandType = CommandType.StoredProcedure

        query.Parameters.AddRange(dsVal.ToArray())
        result = query.ExecuteNonQuery()
        Connect.Close()

        Return result
    End Function

    Public Shared Function ReturnVal(sql As String) As Object
        Dim result As New Object

        Connect.Open()
        Dim query As New SqlCommand(sql, Connect)
        result = query.ExecuteScalar()
        Connect.Close()

        Return result
    End Function
    ''lấy dữ liệu bằng proc
    Public Shared Function GetDataTableByStoreProc(procName As String) As DataTable
        Dim dt As New DataTable
        Dim query As New SqlCommand(procName, Connect)

        query.CommandType = CommandType.StoredProcedure

        Dim adapter As New SqlDataAdapter(query)

        adapter.Fill(dt)

        Return dt
    End Function

    Public Shared Function ThucThiStoreCoThamSo(ByVal tenStore As String, ByVal dsThamSo As List(Of SqlParameter)) As DataTable
        Dim dt As New DataTable
        Connect.Open()
        Dim cmd As New SqlCommand(tenStore, Connect)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddRange(dsThamSo.ToArray())
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(dt)
        Connect.Close()
        Return dt
    End Function

    ''lấy dữ liệu bằng proc trả về ds Reader)
    Public Shared Function GetDataByStoredProReturnArrayReadr(StoreProc As String, dsVal As List(Of SqlParameter)) As List(Of SqlDataReader)
        Dim dsReader As New List(Of SqlDataReader)

        Connect.Open()
        Dim query As New SqlCommand(StoreProc, Connect)
        query.CommandType = CommandType.StoredProcedure
        If dsVal Is Nothing Then
            query.Parameters.AddRange(dsVal.ToArray())
        End If
        Dim reader As SqlDataReader = query.ExecuteReader
        While reader.Read
            dsReader.Add(reader)
        End While
        Connect.Close()
        Return dsReader
    End Function

    Public Shared Function ExecQueryStoreProcReturnValueOfStore(StoreProc As String, dsVal As List(Of SqlParameter), valueReturn As String) As Object
        Dim result As Object

        Connect.Open()
        Dim query As New SqlCommand(StoreProc, Connect)

        query.CommandType = CommandType.StoredProcedure

        query.Parameters.AddRange(dsVal.ToArray())
        query.ExecuteNonQuery()
        result = query.Parameters(valueReturn).Value
        Connect.Close()

        Return result
    End Function
End Class
